package com.ys.controller.api.amazonorder;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.ys.entity.amazonorder.*;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.ys.entity.business.TpQuestion;
import com.ys.entity.sys.YsCol;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.UploadService.UploadDto;
import com.ys.util.ExportExcelUtils;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;

@Controller
@RequestMapping("/api/inter/tpSaleDataSheet")
public class ApiInterTpSaleDataSheetController {
	
	public static Long MENU_ID = 44L;
	
	@Autowired
	private CommonApiService<TpQuestion> commonApiService;
	@RequiresPermissions("tpSaleDataSheet:view")

	@RequestMapping("/search")
    @ResponseBody
	public LayuiTableVoJA search(Model model, HttpServletRequest request, HttpServletResponse response,
			@ModelAttribute TpAmazonShopOrder cond,
			String typeOne,
			String typeTwo,
			String year,
			String month,
			String day,
			String nameYear,
			String nameMonth,
			String nameDay){
		
		Integer _ys_page = cond._ys_page;
		Integer _ys_size = cond._ys_size;
		
		System.out.println(typeOne);
		System.out.println(typeTwo);
		System.out.println(year);
		System.out.println(month);
		System.out.println("day---" + day);
		System.out.println("nameYear---" + nameYear);
		System.out.println("nameMonth---" + nameMonth);
		System.out.println("nameDay---" + nameDay);

		LayuiTableVoJA vo = new LayuiTableVoJA();
		
		String timeCondSql = null;
		if ("year".equals(typeOne)) {
			if (year != null && year.length() > 0) {
				String[] strs = year.split(" ~ ");
				timeCondSql = "A.TimeYear BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			
			if ("all".equals(typeTwo)) {
				TcoYearAll cond2 = new TcoYearAll()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc").where(timeCondSql)
						.setFields("'-' name,A.TimeYear time,A.Num num,A.Amount amount")
						.addField("'-' percentNum")
						.addField("'-' percentAmount");
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());
					
					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("country".equals(typeTwo)) {
				TcoYearCountry cond2 = new TcoYearCountry()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId")
						.addLeftJoin("kjecp_report.dbo.TCO_YearAll C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.CountryName like '%" + nameYear + "%'");
				}

				List<Map<String, Object>> listMap = cond2.queryMap();
				vo.setRowsByListMap(listMap);
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());
					
					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("shop".equals(typeTwo)) {
				TcoYearShop cond2 = new TcoYearShop()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("B.ShopName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonShop B on B.Id = A.ShopId")
						.addLeftJoin("kjecp_report.dbo.TCO_YearAll C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.ShopName like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("product".equals(typeTwo)) {
				TcoYearProduct cond2 = new TcoYearProduct()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearAll C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-youpin".equals(typeTwo)) {
				TcoYearProductYP cond2 = new TcoYearProductYP()
                        .setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearProductYPSum C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-jingpin".equals(typeTwo)) {
				TcoYearProductJP cond2 = new TcoYearProductJP()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearProductJPSum C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}  else if ("product-jingpinE".equals(typeTwo)) {
				TcoYearProductJPEUROPE cond2 = new TcoYearProductJPEUROPE()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearProductJPSum C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-jingpinN".equals(typeTwo)) {
				TcoYearProductJPNORTH cond2 = new TcoYearProductJPNORTH()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearProductJPSum C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("product-cate".equals(typeTwo)) {
				TcoYearProductType cond2 = new TcoYearProductType().where(timeCondSql)
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc")
						.setFields("B.DictName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("YS_Dict B ON B.DictKey = 'TCO_YearProductType-Type' AND B.DictValue = A.Type")
						.addLeftJoin("kjecp_report.dbo.TCO_YearAll C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.name like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("region".equals(typeTwo)) {
				TcoYearRegion cond2 = new TcoYearRegion()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeYear desc,A.Num desc").where(timeCondSql)
						.setFields("B.RegionName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegion B on B.regioncode = A.regioncode")
						.addLeftJoin("kjecp_report.dbo.TCO_YearAll C on C.TimeYear = A.TimeYear")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameYear != null && nameYear.length() > 0) {
					cond2.where("B.ShopName like '%" + nameYear + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}
		} else if ("month".equals(typeOne)) {
			
			if (month != null && month.length() > 0) {
				String[] strs = month.split(" ~ ");
				timeCondSql = "A.TimeMonth BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			if ("all".equals(typeTwo)) {
				TcoMonthAll cond2 = new TcoMonthAll()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("'-' name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addField("'-' percentNum")
						.addField("'-' percentAmount");
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("country".equals(typeTwo)) {
				TcoMonthCountry cond2 = new TcoMonthCountry()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthAll C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.CountryName like '%" + nameMonth + "%'");
				}
				
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("shop".equals(typeTwo)) {
				TcoMonthShop cond2 = new TcoMonthShop()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("B.ShopName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonShop B on B.Id = A.ShopId")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthAll C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.ShopName like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product".equals(typeTwo)) {
				TcoMonthProduct cond2 = new TcoMonthProduct()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthAll C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("product-youpin".equals(typeTwo)) {
				TcoMonthProductYP cond2 = new TcoMonthProductYP()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthProductYPSum C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}
			else if ("product-jingpin".equals(typeTwo)) {
				TcoMonthProductJP cond2 = new TcoMonthProductJP()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthProductJPSum C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-jingpinN".equals(typeTwo)) {
				TcoMonthProductJPNORTH cond2 = new TcoMonthProductJPNORTH()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthProductJPSum C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-jingpinE".equals(typeTwo)) {
				TcoMonthProductJPEUROPE cond2 = new TcoMonthProductJPEUROPE()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthProductJPSum C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("product-cate".equals(typeTwo)) {
				TcoMonthProductType cond2 = new TcoMonthProductType()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("B.DictName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("YS_Dict B ON B.DictKey = 'TCO_MonthProductType-Type' AND B.DictValue = A.Type")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthAll C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.name like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("region".equals(typeTwo)) {
				TcoMonthRegion cond2 = new TcoMonthRegion()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeMonth desc,A.Num desc").where(timeCondSql)
						.setFields("B.RegionName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegion B on B.regioncode = A.regioncode")
						.addLeftJoin("kjecp_report.dbo.TCO_MonthAll C on C.TimeMonth = A.TimeMonth")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameMonth != null && nameMonth.length() > 0) {
					cond2.where("B.ShopName like '%" + nameMonth + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}
		} else if ("day".equals(typeOne)) {
			
			if (day != null && day.length() > 0) {
				String[] strs = day.split(" ~ ");
				timeCondSql = "A.TimeDay BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			
			if ("all".equals(typeTwo)) {
				TcoDayAll cond2 = new TcoDayAll()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("'-' name,A.TimeDay time,A.Num num,A.Amount amount")
						.addField("'-' percentNum")
						.addField("'-' percentAmount");
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("country".equals(typeTwo)) {
				TcoDayCountry cond2 = new TcoDayCountry()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId")
						.addLeftJoin("kjecp_report.dbo.TCO_DayAll C on C.TimeDay = A.TimeDay")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameDay != null && nameDay.length() > 0) {
					cond2.where("B.CountryName like '%" + nameDay + "%'");
				}
				
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			} else if ("region".equals(typeTwo)) {
				TcoDayRegion cond2 = new TcoDayRegion()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("B.RegionName name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegion B on B.regioncode = A.regioncode")
						.addLeftJoin("kjecp_report.dbo.TCO_DayAll C on C.TimeDay = A.TimeDay")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameDay != null && nameDay.length() > 0) {
					cond2.where("B.ShopName like '%" + nameDay + "%'");
				}
				
				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
//			} else if ("product".equals(typeTwo)) {
//				TcoDayProduct cond2 = new TcoDayProduct()
//						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
//						.setFields("(B.code + '-' + B.name) name,A.TimeDay time,A.Num num,A.Amount amount")
//						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
//						.addLeftJoin("kjecp_report.dbo.TCO_DayAll C on C.TimeDay = A.TimeDay")
//						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
//						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
//				if (nameDay != null && nameDay.length() > 0) {
//					cond2.where("B.name like '%" + nameDay + "%'");
//				}
//				
//				vo.setRowsByListMap(cond2.queryMap());
//				vo.setCount(cond2.count());
//				{
//					JSONObject JO = new JSONObject();
//					// 订单数累计
//					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());
//
//					// 订单总金额累计
//					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());
//
//					vo.setTotalRow(JO);
//				}
//			} else if ("product-youpin".equals(typeTwo)) {
//				TcoDayProductYP cond2 = new TcoDayProductYP()
//						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
//						.setFields("(B.code + '//' + B.Name) name,A.TimeDay time,A.Num num,A.Amount amount")
//						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
//						.addLeftJoin("kjecp_report.dbo.TCO_DayAll C on C.TimeDay = A.TimeDay")
//						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
//						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
//				if (nameDay != null && nameDay.length() > 0) {
//					cond2.where("B.name like '%" + nameDay + "%'");
//				}
//
//				vo.setRowsByListMap(cond2.queryMap());
//				vo.setCount(cond2.count());
//				{
//					JSONObject JO = new JSONObject();
//					// 订单数累计
//					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());
//
//					// 订单总金额累计
//					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());
//
//					vo.setTotalRow(JO);
//				}
			} else if ("product-jingpin".equals(typeTwo)) {
				TcoDayProductJP cond2 = new TcoDayProductJP()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.Name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_DayProductJPSum C on C.TimeDay = A.TimeDay")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameDay != null && nameDay.length() > 0) {
					cond2.where("B.name like '%" + nameDay + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("product-jingpinN".equals(typeTwo)) {
				TcoDayProductJPNORTH cond2 = new TcoDayProductJPNORTH()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.Name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_DayProductJPSum C on C.TimeDay = A.TimeDay")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameDay != null && nameDay.length() > 0) {
					cond2.where("B.name like '%" + nameDay + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}else if ("product-jingpinE".equals(typeTwo)) {
				TcoDayProductJPEUROPE cond2 = new TcoDayProductJPEUROPE()
						.setSize(_ys_size).setPage(_ys_page - 1).setOrderby("A.TimeDay desc,A.Num desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.Name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.addLeftJoin("kjecp_report.dbo.TCO_DayProductJPSum C on C.TimeDay = A.TimeDay")
						.addField("Round(A.Num * 100.0 / C.Num, 2) percentNum")
						.addField("Round(A.Amount * 100.0 / C.Amount, 2) percentAmount");
				if (nameDay != null && nameDay.length() > 0) {
					cond2.where("B.name like '%" + nameDay + "%'");
				}

				vo.setRowsByListMap(cond2.queryMap());
				vo.setCount(cond2.count());
				{
					JSONObject JO = new JSONObject();
					// 订单数累计
					JO.put("num", cond2.setFields("ISNULL(SUM(A.Num),0)").sumDecimal());

					// 订单总金额累计
					JO.put("amount", cond2.setFields("ISNULL(SUM(A.Amount),0)").sumDecimal());

					vo.setTotalRow(JO);
				}
			}
		}
		
		return vo;
		
	}
	
	@RequestMapping("/export")
    @ResponseBody
    public R export(@ModelAttribute TpAmazonShopOrderDaily cond,
			String typeOne,
			String typeTwo,
			String year,
			String month,
			String day) throws Exception{
		
		cond.setPage(0).setSize(999999);

		List<Map<String, Object>> listData = null;
		
		String timeCondSql = null;
		if ("year".equals(typeOne)) {
			if (year != null && year.length() > 0) {
				String[] strs = year.split(" ~ ");
				timeCondSql = "A.TimeYear BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			
			if ("all".equals(typeTwo)) {
				TcoYearAll cond2 = new TcoYearAll().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("'-' name,A.TimeYear time,A.Num num,A.Amount amount");
				listData = cond2.queryMap();
			} else if ("country".equals(typeTwo)) {
				TcoYearCountry cond2 = new TcoYearCountry().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId");
				listData = cond2.queryMap();
			} else if ("shop".equals(typeTwo)) {
				TcoYearRegion cond2 = new TcoYearRegion().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.ShopName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonShop B on B.Id = A.ShopId");
				listData = cond2.queryMap();
			} else if ("product".equals(typeTwo)) {
				TcoYearProduct cond2 = new TcoYearProduct().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode");
				listData = cond2.queryMap();
			} else if ("product-youpin".equals(typeTwo)) {
				TcoYearProductYP cond2 = new TcoYearProductYP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.IsSuperior = '是'");
				listData = cond2.queryMap();
			} else if ("product-jingpin".equals(typeTwo)) {
				TcoYearProductJP cond2 = new TcoYearProductJP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.VSfJp = '是'");
				listData = cond2.queryMap();
			} else if ("product-cate".equals(typeTwo)) {
				TcoYearProductType cond2 = new TcoYearProductType().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.DictName name,A.TimeYear time,A.Num num,A.Amount amount")
						.addLeftJoin("YS_Dict B ON B.DictKey = 'TCO_YearProductType-Type' AND B.DictValue = A.Type");
				listData = cond2.queryMap();
			}
		} else if ("month".equals(typeOne)) {
			
			if (month != null && month.length() > 0) {
				String[] strs = month.split(" ~ ");
				timeCondSql = "A.TimeMonth BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			if ("all".equals(typeTwo)) {
				TcoMonthAll cond2 = new TcoMonthAll().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("'-' name,A.TimeMonth time,A.Num num,A.Amount amount");
				listData = cond2.queryMap();
			} else if ("country".equals(typeTwo)) {
				TcoMonthCountry cond2 = new TcoMonthCountry().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId");
				listData = cond2.queryMap();
			} else if ("shop".equals(typeTwo)) {
				TcoMonthRegion cond2 = new TcoMonthRegion().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.ShopName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonShop B on B.Id = A.ShopId");
				listData = cond2.queryMap();
			} else if ("product".equals(typeTwo)) {
				TcoMonthProduct cond2 = new TcoMonthProduct().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode");
				listData = cond2.queryMap();
			}else if ("product-youpin".equals(typeTwo)) {
				TcoMonthProductYP cond2 = new TcoMonthProductYP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.IsSuperior = '是'");
				listData = cond2.queryMap();
			} else if ("product-jingpin".equals(typeTwo)) {
				TcoMonthProductJP cond2 = new TcoMonthProductJP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '//' + B.name) name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.VSfJp = '是'");
				listData = cond2.queryMap();
			} else if ("product-cate".equals(typeTwo)) {
				TcoMonthProductType cond2 = new TcoMonthProductType().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.DictName name,A.TimeMonth time,A.Num num,A.Amount amount")
						.addLeftJoin("YS_Dict B ON B.DictKey = 'TCO_MonthProductType-Type' AND B.DictValue = A.Type");
				listData = cond2.queryMap();
			}
		} else if ("day".equals(typeOne)) {
			
			if (day != null && day.length() > 0) {
				String[] strs = day.split(" ~ ");
				timeCondSql = "A.TimeDay BETWEEN '" + strs[0] + "' AND '" + strs[1] + "'";
			}
			
			if ("all".equals(typeTwo)) {
				TcoDayAll cond2 = new TcoDayAll().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("'-' name,A.TimeDay time,A.Num num,A.Amount amount");
				listData = cond2.queryMap();
			} else if ("country".equals(typeTwo)) {
				TcoDayCountry cond2 = new TcoDayCountry().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.CountryName name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonRegionItem B on B.MarketPlaceId = A.MarketPlaceId");
				listData = cond2.queryMap();
			} else if ("shop".equals(typeTwo)) {
				TcoDayRegion cond2 = new TcoDayRegion().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.ShopName name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_AmazonShop B on B.Id = A.ShopId");
				listData = cond2.queryMap();
			} else if ("product".equals(typeTwo)) {
				TcoDayProduct cond2 = new TcoDayProduct().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode");
				listData = cond2.queryMap();
			} else if ("product-youpin".equals(typeTwo)) {
				TcoDayProductYP cond2 = new TcoDayProductYP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.IsSuperior = '是'");
				listData = cond2.queryMap();
			} else if ("product-jingpin".equals(typeTwo)) {
				TcoDayProductJP cond2 = new TcoDayProductJP().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("(B.code + '-' + B.name) name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("TP_ProductActive B on B.code = A.ProductCode")
						.where("B.VSfJp = '是'");
				listData = cond2.queryMap();
			} else if ("product-cate".equals(typeTwo)) {
				TcoDayProductType cond2 = new TcoDayProductType().setOrderby("A.Id desc").where(timeCondSql)
						.setFields("B.DictName name,A.TimeDay time,A.Num num,A.Amount amount")
						.addLeftJoin("YS_Dict B ON B.DictKey = 'TCO_DayProductType-Type' AND B.DictValue = A.Type");
				listData = cond2.queryMap();
			}
		}
		
		System.out.println(JSON.toJSONString(listData));
        
    	List<YsCol> listField = new ArrayList<YsCol>();
    	listField.add(new YsCol().setCode("name").setName("项目"));
    	listField.add(new YsCol().setCode("time").setName("时间"));
    	listField.add(new YsCol().setCode("num").setName("数量"));
    	listField.add(new YsCol().setCode("amount").setName("金额"));
    	
    	
        ExportExcelUtils<TpAmazonShopOrderDaily> util = new ExportExcelUtils<TpAmazonShopOrderDaily>(new TpAmazonShopOrderDaily());
        
        String toFilePath = util.exportExcel(listData, listField, "销售数据表");
        UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
        return R.ok(dto.getUrl());
        
    }

}
	
